﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Text;

namespace OracleDbTool
{

    public static class OrclFunc
    {
        private static OracleConnection m_ConnLocal; //Oracle连接
        private static string m_strConnLocal = null;//本地连接串，当不为空时连接本地
        static OrclFunc()
        {
        }


        /// <summary>
        /// 设置连接串并打开共享连接
        /// </summary>
        /// <param name="strConn">oracle 连接串</param>
        public static void OpenLocalConnection(string strConn)
        {
            m_strConnLocal = strConn;
            m_ConnLocal = new OracleConnection(strConn);
            m_ConnLocal.Open();
        }
        /// <summary>
        /// 关闭共享连接
        /// </summary>
        public static void CloseLocalConnection()
        {
            if (m_ConnLocal != null)
            {
                if (m_ConnLocal.State != ConnectionState.Closed)
                    m_ConnLocal.Close();
                m_strConnLocal = null;
            }
        }


        //private static System.Data.OracleClient.OracleParameter ToOracleClientParameter(OracleParameter op)
        //{
        //    System.Data.OracleClient.OracleParameter op2 = new System.Data.OracleClient.OracleParameter();
        //    op2.OracleType = (System.Data.OracleClient.OracleType)Enum.Parse(typeof(System.Data.OracleClient.OracleType), Enum.GetName(typeof(OracleType), op.OracleType));
        //    op2.Direction = (System.Data.ParameterDirection)Enum.Parse(typeof(System.Data.ParameterDirection), Enum.GetName(typeof(ParameterDirection), op.Direction));
        //    op2.ParameterName = op.ParameterName.Substring(0, op.ParameterName.Length - 1);
        //    op2.Size = op.Size;
        //    if (op.Value != null)
        //        op2.Value = op.Value;
        //    return op2;
        //}

        /// <summary>
        /// 本地方法:调用ORACLE返回结果集的存储过程/函数
        /// 限制:返回REF CURSOR,不能同时再有Output/InputOutput参数
        /// </summary>
        /// <param name="sp_name">存储过程或函数名称</param>
        /// <param name="ops">参数列表</param>
        public static DataSet StoredProcDSLocal(OracleParameter[] ops, string sp_name)
        {//输入输出参数须按数据库的相应名称设定
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand cmd = new OracleCommand(sp_name, m_ConnLocal);
            cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < ops.Length; i++)
                cmd.Parameters.Add(ops[i]);
            //返回结果集
            DataSet ds = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            //可能返回多个ref cursor
            int j = 0;
            for (int i = 0; i < ops.Length; i++)
            {
                if (ops[i].OracleDbType == OracleDbType.RefCursor)
                    da.Fill(ds, "TABLE" + (j++).ToString());
            }

            return ds;
        }

        /// <summary>
        /// 本地方法:调用ORACLE一般存储过程（仅有参数）
        /// </summary>
        /// <param name="sp_name">存储过程或函数名称</param>
        /// <param name="ops">参数列表</param>
        public static void StoredProcLocal(ref OracleParameter[] ops, string sp_name)
        {//输入输出参数须按数据库的相应名称设定
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand cmd = new OracleCommand(sp_name, m_ConnLocal);
            cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < ops.Length; i++)
                cmd.Parameters.Add(ops[i]);

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 本地方法:单值查询返回字符串
        /// </summary>
        /// <param name="sql">sql语句</param>
        public static string ExecuteScalarStrLocal(string sql)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand cmd = new OracleCommand(sql, m_ConnLocal);
            object v = cmd.ExecuteScalar();
            return v.ToString();
        }

        /// <summary>
        /// 本地方法:单值查询返回整型
        /// </summary>
        /// <param name="sql">sql语句</param>
        public static int ExecuteScalarIntLocal(string sql)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand cmd = new OracleCommand(sql, m_ConnLocal);
            object v = cmd.ExecuteScalar();
            return int.Parse(v.ToString());
        }

        /// <summary>
        /// 本地方法:sql查询返回结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        public static DataSet QueryBySqlLocal(string sql)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();
            OracleDataAdapter oda = new OracleDataAdapter(sql, m_ConnLocal);
            DataSet ds = new DataSet();
            oda.Fill(ds);

            return ds;
        }

        /// <summary>
        /// 本地方法:带绑定变量sql查询返回结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        public static DataSet QueryBySqlLocal(string sql, OracleParameter[] ops)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand cmd = new OracleCommand(sql, m_ConnLocal);
            cmd.Parameters.AddRange(ops);
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 本地方法:执行sql命令insert/update等
        /// </summary>
        /// <param name="cmd">sql语句</param>
        public static int ExecuteNonQueryLocal(string cmd)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand ocmd = new OracleCommand(cmd, m_ConnLocal);
            return (ocmd.ExecuteNonQuery());
        }

        /// <summary>
        /// 本地方法:执行sql命令insert/update等
        /// </summary>
        /// <param name="cmd">sql语句</param>
        /// <param name="cmd">sql语句</param>
        public static int ExecuteNonQueryLocal(string cmd, OracleParameter[] ops)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            OracleCommand ocmd = new OracleCommand(cmd, m_ConnLocal);
            ocmd.Parameters.AddRange(ops);
            return (ocmd.ExecuteNonQuery());
        }

        public static int ExecNonQueryTransLocal(string[] cmds, int cmd_count)
        {
            if (m_ConnLocal.State == ConnectionState.Closed)
                m_ConnLocal.Open();

            int j = 0;
            OracleCommand cmd = m_ConnLocal.CreateCommand();
            // Start a local transaction
            OracleTransaction myTrans;
            myTrans = m_ConnLocal.BeginTransaction(IsolationLevel.ReadCommitted);
            cmd.Transaction = myTrans;

            for (int i = 0; i < cmd_count; i++)
            {
                cmd.CommandText = cmds[i];
                j = j + cmd.ExecuteNonQuery();
            }
            myTrans.Commit();
            return j;
        }
    }
}
